# -*- coding: utf-8 -*-
from openerp.osv import osv, fields
import datetime
import pytz
import random
from openerp.tools.translate import _
from openerp import models,SUPERUSER_ID


#卡券和非卡券产品按天缓存统计表
#由于订单统计缺少卡券激活部分，因此这张表中加入非卡券产品
class product_card_stat_day(osv.osv):
    _name = "product.card.stat.day"
    _description = u'卡券销售统计'
    _columns = {
        'date': fields.date(u'日期'), #定时任务，从track和订单表冗余到这个统计表
        'product_id': fields.many2one('product.product', u'产品', required=True, domain="[('is_card','=', False)]" ),
        'owner_id': fields.many2one('res.partner', u'归属方'),
        'client_id': fields.many2one('res.partner', u'客户'),
        'salesman': fields.many2one('res.users',u'业务员'), #业务员
        'state': fields.char(u'状态'), #状态类型（卖出、退回、激活、取消激活）
        'quantity': fields.integer(u'数量'),#退回和取消激活用负数表示
        'amount_total': fields.float(u'金额合计'),
    }

    #建立某天统计
    def _create_stat_day(self, cr, uid, date, row, context=None):
        product_id = row['product_id']
        is_act_product = False
        client_id = row['order_partner_id']
        salesman = row['salesman_id']
        quantity = row['qty']
        amount_total = row['total']
        #将激活产品转为正常产品
        cr.execute("SELECT pt.* FROM product_template pt INNER JOIN product_product pp ON pp.product_tmpl_id = pt.id WHERE pt.id=%s" % (product_id,))
        product_template = cr.dictfetchone()
        is_card = product_template['is_card']
        product_type = product_template['type']
        if product_type == 'service':
            #如果产品类型是服务并且是卡券，则说明是激活产品，则将产品编号变为对应的那个非激活产品
            if is_card:
                #取得激活服务对应的产品
                cr.execute("SELECT pp.id FROM product_template pt INNER JOIN product_product pp ON pp.product_tmpl_id = pt.id WHERE pt.act_id=%s" % (product_id,))
                product_template = cr.dictfetchone()
                if product_template:
                    product_id = product_template['id']
                    is_act_product = True
            #其他服务产品，如优惠、运费等
            else:
                pass

        state = '卖出'
        if is_card:
            state = '铺券'
        if is_act_product:
            state = '激活'
        if row['is_return']:
            state = '退回'
            if is_act_product:
                state = '取消激活'

        self.create(cr, uid, {
                'date': date,
                'product_id': product_id,
                'client_id': client_id,
                'salesman': salesman,
                'quantity': quantity,
                'state': state,
                'amount_total': amount_total}, context=context)

    #按天统计任务
    def summary(self, cr, uid, date=None, context=None):
        if not date:
            #取昨天的确认订单列表
            date = (datetime.datetime.now() - datetime.timedelta(days=1))
            date = date.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
            date = date.strftime("%Y-%m-%d")

        cr.execute('''
            -- 卡券铺券订单
            SELECT DATE(sp.date_done) as stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return as is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
              INNER JOIN (SELECT DISTINCT ON (origin) * FROM stock_picking WHERE stock_picking.state='done') sp
              ON sp.origin=so.name
            WHERE so.is_card = TRUE AND so.date_confirm IS NOT NULL AND DATE(sp.date_done)='%s' AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            UNION
            -- 卡券激活订单(没有发货单，确认就进行统计)
            SELECT DATE(so.date_confirm) as stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return as is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
            LEFT JOIN stock_picking sp ON sp.origin=so.name
            WHERE sp.origin IS NULL AND DATE(so.date_confirm)='%s' AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            UNION
            -- 实物订单(不需要发货，只要确认就进行统计)
            SELECT DATE(so.date_confirm) AS stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return AS is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
            LEFT JOIN stock_picking sp ON sp.origin=so.name
            WHERE so.is_card = FALSE AND DATE(so.date_confirm)='%s' AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            ''' % (date, date, date))

        rows = cr.dictfetchall()
        #防止万一有重复的记录，先删除
        cr.execute("DELETE FROM product_card_stat_day WHERE date=%s", (date,))
        for row in rows:
            self._create_stat_day(cr, uid, date, row)
        return True

    #刷新当天订单action
    def summary_today(self, cr, uid, context=None):
        today = datetime.datetime.now()
        today = today.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
        today = today.strftime("%Y-%m-%d")
        self.summary(cr, uid, today)
        return True

    #刷新时间段
    def summary_all(self, cr, uid, context=None):
        #取得有出库单并且部分或全部出库的订单行日期汇总
        #UNION
        #取得卡券激活（没有出库单）确认的订单行日期汇总
        cr.execute('''
            -- 卡券铺券订单
            SELECT DATE(sp.date_done) as stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return as is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
              INNER JOIN (SELECT DISTINCT ON (origin) * FROM stock_picking WHERE stock_picking.state='done') sp
              ON sp.origin=so.name
            WHERE so.is_card = TRUE AND so.date_confirm IS NOT NULL AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            UNION
            -- 卡券激活订单(没有发货单，确认就进行统计)
            SELECT DATE(so.date_confirm) as stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return as is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
            LEFT JOIN stock_picking sp ON sp.origin=so.name
            WHERE so.date_confirm IS NOT NULL AND sp.origin IS NULL AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            UNION
            -- 实物订单(不需要发货，只要确认就进行统计)
            SELECT DATE(so.date_confirm) AS stat_date, sol.product_id, sol.order_partner_id,
            sol.salesman_id, so.is_return AS is_return,
            sum(sol.product_uom_qty) AS qty, sum(sol.product_uom_qty * sol.price_unit) AS total
            FROM sale_order so INNER JOIN sale_order_line sol ON so.id = sol.order_id
            LEFT JOIN stock_picking sp ON sp.origin=so.name
            WHERE so.is_card = FALSE AND so.date_confirm IS NOT NULL AND so.state != 'cancel'
            GROUP BY stat_date, sol.product_id, sol.order_partner_id, sol.salesman_id, so.is_return
            ORDER BY stat_date
            ''')
        rows = cr.dictfetchall()

        #先删除所有
        cr.execute("DELETE FROM product_card_stat_day")
        for row in rows:
            date = row['stat_date']
            self._create_stat_day(cr, uid, date, row)

        return True
